package Impl;

import Util.JDBC;
import dao.plateDao;
import entity.Admin;
import entity.Plate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

public class plateDaoImpl implements plateDao {
    adminDaoImpl adminDao = new adminDaoImpl();
    Connection conn=null;
    PreparedStatement ps=null;
    ResultSet rs =null;
    boolean valid=true;
    @Override
    public void addPlate(Plate plate) {
        System.out.println("正在新增板块：");
        String sql = "insert into plate (sName,sTopicCount,sMasterId,sParentId,sTime) " +
                "values(?,?,?,?,?)";
        Date currentTime = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");
        String dateString = formatter.format(currentTime);
        Object[] params = {plate.getsName(),0,plate.getsMasterId(),plate.getsParentId(),dateString};
        adminDao.commonUp(sql,params);
        System.out.println("新增板块成功！");
    }

    @Override
    public void deletePlateBysId(int sid) {
        String sql ="delete from plate where sid=?";
        adminDao.commonUp(sql,sid);
        System.out.println("成功删除板块");
    }

    @Override
    public List<Plate> findPlateByPages(int pageNow) {
        int pageCount = 5;
        conn = JDBC.getConnection();
        List<Plate> plateList = new ArrayList<>();
        String sql = "select * from plate limit ?,?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1, (pageNow-1)*pageCount);
            ps.setObject(2, pageCount);
            rs = ps.executeQuery();
            while(rs.next()) {
                Plate tmp = new Plate();
                tmp.setSid(rs.getInt("sid"));
                tmp.setsMasterId(rs.getInt("sMasterId"));
                tmp.setsName(rs.getString("sname"));
                tmp.setsParentId(rs.getInt("sParentId"));
                tmp.setsTopicCount(rs.getInt("sTopicCount"));
                tmp.setsTime(rs.getString("sTime"));
                plateList.add(tmp);
            }
            System.out.println("分页查询成功");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            JDBC.closeAll(conn,ps,rs);
        }
        return plateList;

    }

    @Override
    public List<Plate> findAllPlate() {
        List<Plate> plates = new ArrayList<>();
        String sql = "select * from plate";
        try {
            conn= JDBC.getConnection();
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while (rs.next()){
                Plate tmp = new Plate();
                tmp.setSid(rs.getInt("sid"));
                tmp.setsMasterId(rs.getInt("sMasterId"));
                tmp.setsName(rs.getString("sName"));
                tmp.setsParentId(rs.getInt("sParentId"));
                tmp.setsTopicCount(rs.getInt("sTopicCount"));
                tmp.setsTime(rs.getString("sTime"));
                plates.add(tmp);
            }
            System.out.println("查找成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBC.closeAll(conn,ps,rs);
        }
        return plates;
    }

    @Override
    public void updatePlate(Plate plate) {
        String sql="update plate set sName=? , sMasterId=? , sParentId=?,sTime=? where sid=?";
        Date currentTime = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");
        String dateString = formatter.format(currentTime);
        Object[] params = {plate.getsName(),plate.getsMasterId(),plate.getsParentId(),dateString,plate.getSid()};
        adminDao.commonUp(sql,params);
        System.out.println("更新板块信息成功！");
    }

    @Override
    public List<Plate> findPlateByCondition(String condition) {
        List<Plate> plates = new ArrayList<>();
        String sql="select * from plate where sname='"+condition+"'";
        try {
            conn= JDBC.getConnection();
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while (rs.next()){
                Plate tmp = new Plate();
                tmp.setSid(rs.getInt("sid"));
                tmp.setsMasterId(rs.getInt("sMasterId"));
                tmp.setsName(rs.getString("sName"));
                tmp.setsParentId(rs.getInt("sParentId"));
                tmp.setsTopicCount(rs.getInt("sTopicCount"));
                tmp.setsTime(rs.getString("sTime"));
                plates.add(tmp);
            }
            System.out.println("查找成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBC.closeAll(conn,ps,rs);
        }
        return plates;
    }

    @Override
    public List<Plate> findPlateByPId(int pid) {
        List<Plate> platesList = new ArrayList<>();
        String sql ="select * from plate where sParentId="+pid;
        try {
            conn= JDBC.getConnection();
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while (rs.next()){
                Plate tmp = new Plate();
                tmp.setSid(rs.getInt("sid"));
                tmp.setsMasterId(rs.getInt("sMasterId"));
                tmp.setsName(rs.getString("sName"));
                tmp.setsParentId(rs.getInt("sParentId"));
                tmp.setsTopicCount(rs.getInt("sTopicCount"));
                tmp.setsTime(rs.getString("sTime"));
                platesList.add(tmp);
            }
            System.out.println("查找成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBC.closeAll(conn,ps,rs);
        }
        return platesList;
    }

    @Override
    public List<Plate> findPlateBySid(int sid) {
        List<Plate> platesList = new ArrayList<>();
        String sql ="select * from plate where sid="+sid;
        try {
            conn= JDBC.getConnection();
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while (rs.next()){
                Plate tmp = new Plate();
                tmp.setSid(rs.getInt("sid"));
                tmp.setsMasterId(rs.getInt("sMasterId"));
                tmp.setsName(rs.getString("sName"));
                tmp.setsParentId(rs.getInt("sParentId"));
                tmp.setsTopicCount(rs.getInt("sTopicCount"));
                tmp.setsTime(rs.getString("sTime"));
                platesList.add(tmp);
            }
            System.out.println("查找成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBC.closeAll(conn,ps,rs);
        }
        return platesList;
    }

    public void commonUp(String sql,Object ...args){
        try {
            conn= JDBC.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i <args.length ; i++) {
                ps.setObject(i+1,args[i]);
            }
            ps.execute();
            System.out.println("操作完成");
        } catch (SQLException e) {
            System.out.println("操作失败");
            e.printStackTrace();
            valid=false;
        }finally {
            //关闭资源
            JDBC.closeAll(conn,ps,null);
        }
    }
    @Override
    public boolean movePlate(Integer sId, Integer sParentId) {
        String sql="update plate set sParentId=?,sTime=? where sId=?";
        Date currentTime = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");
        String dateString = formatter.format(currentTime);
        Object[] params={sParentId,dateString,sId};
        commonUp(sql,params);
        System.out.println("移动成功");
        return valid;
    }

    @Override
    public ArrayList<Plate> findAllChildPlate(){
        ArrayList<Plate> plates = new ArrayList<>();
        String sql = "select * from plate where sParentId!=0";
        try {
            conn=JDBC.getConnection();
            ps = conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while (rs.next()){
                Plate tmp = new Plate();
                tmp.setSid(rs.getInt("sid"));
                tmp.setsTime(rs.getString("sTime"));
                tmp.setsName(rs.getString("sName"));
                tmp.setsTopicCount(rs.getInt("sTopicCount"));
                tmp.setsMasterId(rs.getInt("sMasterId"));
                tmp.setsParentId(rs.getInt("sParentId"));
                plates.add(tmp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBC.closeAll(conn,ps,rs);
        }
        return plates;
    }

    @Override
    public void updateTopicCounts(int sid) {
        String sql ="update plate set sTopicCount =" +
                "(select a.amount from (select sum(sTopicCount) amount from plate where sParentId=?) a),sTime=?" +
                " where sid=?";

        Date currentTime = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");
        String dateString = formatter.format(currentTime);
        Object[] param = {sid,dateString,sid};
        commonUp(sql,param);
        System.out.println("PlateDaoImpl.java: " + sid);

    }

    @Override
    public Plate findParentPlate(int sid) {
        String sql="select * from plate where sParentId="+sid;
        Plate tmp = new Plate();
        try {
            conn=JDBC.getConnection();
            ps = conn.prepareStatement(sql);
            rs=ps.executeQuery();
            if (rs.next()){
                tmp.setSid(rs.getInt("sid"));
                tmp.setsTime(rs.getString("sTime"));
                tmp.setsName(rs.getString("sName"));
                tmp.setsTopicCount(rs.getInt("sTopicCount"));
                tmp.setsMasterId(rs.getInt("sMasterId"));
                tmp.setsParentId(rs.getInt("sParentId"));
            }else{
                tmp=null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBC.closeAll(conn,ps,rs);
        }
    return tmp;
    }

}
